import mysql.connector 
import json
# from flask_Commodity import Commodity

from flask_mysqldb import MySQL
from flask import Flask, request, jsonify, redirect, render_template, session,url_for,session 

app = Flask(__name__)
app.secret_key = 'your_secret_key'
app.config['PER_PAGE'] = 20  # 设置每页显示的商品数量


# 连接到MySQL数据库
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="123456",
    database="a",
    auth_plugin='mysql_native_password'
)

# 获取数据库游标
cursor = db.cursor()
#登录页面(运行进入的第一个页面)
@app.route('/')
def login1():
     return render_template('login.html')
# 定义登录接口
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']  # 使用form而不是method
        password = request.form['password']  # 使用form而不是method
        query = "SELECT * FROM userpassword WHERE uid = %s AND password = %s"
        cursor.execute(query, (username, password))
        user = cursor.fetchone()
        if user:
            session['username'] = username
            session['password'] = password
            return redirect('/index')  # 登录成功后重定向到index页面
        else:
            h = '账号或密码错误!'
            return render_template('login.html',h=h)
    return render_template('login.html')







#注册页面
# 假设您有一个名为 userpassword 的用户数据表
# 在这里通常您需要连接数据库并导入相应的模块
@app.route('/register', methods=['GET', 'POST'])
def register1():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        # 检查用户名是否已存在
        query_check = "SELECT * FROM userpassword WHERE Uid = %s"
        cursor.execute(query_check, (username,))
        existing_user = cursor.fetchone()
        if existing_user:
                error_message = "用户名已存在，请选择其他用户名"
                return render_template('register.html', error=error_message)

        else:    # 如果用户名不存在，则执行插入操作
            query_insert = "INSERT INTO userpassword (Uid, Password) VALUES (%s, %s)"
            cursor.execute(query_insert, (username, password))
            db.commit()
                
            return redirect(url_for('index'))  # 注册成功后重定向到主页页面
    return render_template('register.html')




#修改密码路由及逻辑
@app.route('/change_password', methods=['GET', 'POST'])
def change_password():
    if request.method == 'POST':
        # username = session.get('username')
        username = request.form['username']
        current_password = request.form['current_password']
        new_password = request.form['new_password']
        
        # 先验证当前密码是否正确
        query_check_password = "SELECT * FROM userpassword WHERE uid = %s AND password = %s"
        cursor.execute(query_check_password, (username, current_password))
        user = cursor.fetchone()
        if user:
            # 当前密码正确，执行修改密码操作
            query_update_password = "UPDATE userpassword SET password = %s WHERE uid = %s"
            cursor.execute(query_update_password, (new_password, username))
            db.commit()
            return redirect('/index')
        else:
            a='nn'
            return render_template('change_password.html',a=a)
    return render_template('change_password.html')
    
#获取主页，商品页
@app.route('/index', methods=['GET'])
def index():
    if 'username' in session:
        return render_template('index.html')
    return redirect('/login')

#列展商品
@app.route('/indexs', methods=['GET'])
def get_indexs():
    try:
        cursor.execute("SELECT * FROM commodity")
        results = cursor.fetchall()
        index_list = []
        for row in results:
            index = {
                'image': row[12],
                'id': row[0],
                'name': row[2],
                'price': row[5],
                'sort': row[6]
            }
            index_list.append(index)
        
        return jsonify(index_list), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500






# mysql = MySQL(app)
#个人信息页面
@app.route('/profile')
def profile():
    return render_template('profile.html')


# 定义个人信息页面路由
# @app.route('/profile', methods=['GET'])
# def profiles():
#         username = session.get('username')
        
#         if 'username' in session:
#              username = session['username']
#              cursor = mysql.connection.cursor()
#              cursor.execute("SELECT * FROM userinformation WHERE Uid = %s", (username,))
#              user = cursor.fetchone()
#              cursor.close()
#              if user:
#                  return render_template('profile.html', user=user)
#              else:
#                 #  return redirect(url_for('login'))
#                 return render_template('login.html')
#         else:
#              return render_template('login.html')

# # 定义编辑个人信息路由
# @app.route('/edit_profile', methods=['POST'])
# def edit_profile():
#     # 检查用户是否已经登录
#     if 'username' in session:
#         username = session['username']
#         # 查询用户信息
#         cursor = mysql.connection.cursor()
#         cursor.execute("SELECT * FROM users WHERE Uid = %s", (username,))
#         user = cursor.fetchone()
#         if user:
#             # 更新用户信息
#             new_username = request.form['new_username']
#             new_phone = request.form['new_phone']
#             new_realname = request.form['new_realname']
#             new_class = request.form['new_class']
#             new_dormitory = request.form['new_dormitory']
#             new_gender = request.form['new_gender']
#             cursor.execute("UPDATE users SET username = %s, phone = %s, realname = %s, class = %s, dormitory = %s, gender = %s WHERE username = %s",
#                            (new_username, new_phone, new_realname, new_class, new_dormitory, new_gender, username))
#             mysql.connection.commit()
#             # 重定向到个人信息页面
#             return redirect(url_for('profile'))
#         else:
#             # 如果用户信息
#             return redirect(url_for('profile'))




#商品详情
@app.route('/Product_Details', methods=['GET'])
def ProductDetails():
    return render_template('Product_Details.html')

#获取购物车页面
@app.route('/ShoppingCar')
def ShoppingCar():
    return render_template('ShoppingCar.html')



#获取我的收藏页面
@app.route('/favorites')
def favorites():
    return render_template('favorites.html')




# 列表展示收藏商品信息
@app.route('/favoritess', methods=['GET'])
def get_favoritess():
    # uid = request.args.get('uid')  # 获取登录时的用户ID  uid = %s  , (uid,)  favorites
    try:
        cursor.execute("SELECT * FROM favorites")
        results = cursor.fetchall()
        favorites_list = []
        for row in results:
            favorites = {
                'id': row[0],
                'name': row[5],
                'price': row[6],
                'sort': row[7],
            }
            favorites_list.append(favorites)
        
        return jsonify(favorites_list), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500








#发布商品页面
@app.route('/suopping')
def supping():
    return render_template('suopping.html')


# 列表展示发布商品信息
@app.route('/suoppings', methods=['GET'])
def get_suoppings():
    try:
        # SELECT * FROM your_table_name WHERE uid = '445566';
        username = session['username']
        cursor.execute("SELECT * FROM commodity WHERE uid = %s", (username,))#检索与登录时的用户名相关的数据
        # cursor.execute("SELECT * FROM commodity WHERE uid = (SELECT uid FROM users WHERE username = %s)", (username,))
        # cursor.execute("SELECT * FROM commodity")
        results = cursor.fetchall()
        suopping_list = []
        for row in results:
            suopping = {
                'id': row[0],
                'name': row[2],
                'price': row[5],
                'count': row[7],
                'sort': row[6],
                'image': row[12],
                'transaction': row[9],
                'uid': row[11],
                'modify': row[1],
            }
            suopping_list.append(suopping)
        
        return jsonify(suopping_list), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

# 发布商品信息功能
@app.route('/suoppings', methods=['POST'])
def add_suopping():
    new_suopping = request.json  # 假设您接收到JSON数据
    if 'usename' in session:
        username = session['username']
        new_suopping = {
            'id': request.json.get('id'),
            'name': request.json.get('name'),
            'level': request.json.get('level'),
            'remark': request.json.get('remark'),
            'price': request.json.get('price'),
            'sort': request.json.get('sort'),
            'count': request.json.get('count'),
            'image': request.json.get('image'),
            'transaction': request.json.get('transaction'),
            'uid': username
        }
        # 插入到MySQL数据库
    insert_query = "INSERT INTO commodity ( name, level, remark, price, sort, count, image, transaction, uid) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
    cursor.execute(insert_query, (new_suopping['name'], new_suopping['level'], new_suopping['remark'], new_suopping['price'], new_suopping['sort'], new_suopping['count'], new_suopping['image'] ,new_suopping['transaction'],new_suopping['uid']))
    db.commit()  # 提交事务
    new_suopping['name'] = cursor.lastrowid  # 获取插入记录的
    return jsonify(new_suopping)


# 获取单个商品信息
@app.route('/suoppings/<int:id>', methods=['GET'])
def get_suopping(id):
    # 使用 SQL 查询语句从数据库中获取课程信息
    query = "SELECT * FROM commodity WHERE id=%s"
    cursor.execute(query, (id,))
    suopping = cursor.fetchone()
    if suopping:
        # username = session['username']
        suopping_info = {
            'id': suopping[0],
            'name': suopping[2],
            'level': suopping[3],
            'remark': suopping[4],
            'price': suopping[5],
            'sort': suopping[6],
            'count': suopping[7],
            'image': suopping[12],
            'transaction': suopping[9],
            'uid': suopping[11]
        }
        return jsonify(suopping_info)
    else:
        return jsonify({'error': 'suopping not found'})


#修改发布商品信息
@app.route('/suoppings/<int:id>', methods=['PUT'])
def update_suopping(id):
    try:
            data = request.get_json()
        # 验证数据存在并且包含所需字段
            if not data or not all(key in data for key in ('name', 'level', 'remark', 'price', 'sort', 'count', 'image', 'transaction')):
                return jsonify({'error': '无效的JSON数据或缺少字段'}), 400
            # 使用 SQL UPDATE 语句更新学生信息
            # cursor.execute("SELECT * FROM commodity")
            update_query = "UPDATE commodity SET name=%s, level=%s, remark=%s, price=%s, sort=%s, count=%s, image=%s, transaction=%s WHERE id=%s"
            # a=SELECT * FROM commodity;
            cursor.execute(update_query, (
                    data['name'], data['level'], data['remark'], data['price'], 
                    data['sort'], data['count'], data['image'], data['transaction'], id
                ))
            # cursor.execute(update_query, (request.json['name'], request.json['level'], request.json['remark'], request.json['price'], request.json['sort'], request.json['count'], request.json['image'], request.json['transaction'], id))
            db.commit()
            if cursor.rowcount > 0:
                return jsonify({'result': True})
            else:
                return jsonify({'error': '未找到商品或未应用任何更改'}),404

    except Exception as e:
        return jsonify({'error': str(e)}), 500
# 修改发布商品信息



# # 路由用于更新商品信息
# @app.route('/suoppings/<int:id>', methods=['PUT'])
# def update_shopping(id):
#     try:
#         data = request.get_json()
        
#         # 验证JSON数据和所需字段
#         if not data or not all(key in data for key in ('name', 'level', 'remark', 'price', 'sort', 'count', 'image', 'transaction')):
#             return jsonify({'error': '无效的JSON数据或缺少字段'}), 400
        
#         # SQL UPDATE语句更新商品信息
#         update_query = "UPDATE commodity SET name=%s, level=%s, remark=%s, price=%s, sort=%s, count=%s, image=%s, transaction=%s WHERE id=%s"
        
#         # 执行更新查询
#         cursor.execute(update_query, (
#             data['name'], data['level'], data['remark'], data['price'], 
#             data['sort'], data['count'], data['image'], data['transaction'], id
#         ))
        
#         # 提交数据库更改
#         db.commit()
        
#         # 检查是否有行受到影响
#         if cursor.rowcount > 0:
#             return jsonify({'result': True})
#         else:
#             return jsonify({'error': '未找到商品或未应用任何更改'}), 404

#     except Exception as e:
#         return jsonify({'error': str(e)}), 500



# 删除商品信息
@app.route('/suoppings/<int:id>', methods=['DELETE'])
def delete_suopping(id):
    # 使用 SQL DELETE 语句从数据库中删除成绩信息
    delete_query = "DELETE FROM commodity WHERE id=%s"
    cursor.execute(delete_query, (id,))
    db.commit()
    if cursor.rowcount > 0:
        return jsonify({'result': True})
    else:
        return jsonify({'error': 'suopping not found'})


# 订单项目数据
# order_items = [
#     {'productId': 1, 'productName': 'Product A', 'quantity': 2, 'unitPrice': 10.00},
#     {'productId': 2, 'productName': 'Product B', 'quantity': 3, 'unitPrice': 20.00},
#     # ...
# ]

# 订单总数据
order_total = 0.0

# API 端点
@app.route('/order_items')
def get_order_items():
    return render_template('order_items.html')


 
    


@app.route('/nav')#导航栏
def nav():
    return render_template('nav.html')

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=8000)
